﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text;

/// <summary>
/// Summary description for User
/// </summary>
public class _User
{
    public _User()
    {
        
    }
    
    public int _Id { get; set; }
    public string _Name { get; set; }
    public string _SecondName { get; set; }
    public string _Login { get; set; }
    public string _Password { get; set; }
    public string _Email { get; set; }
    public int _ID_Licence { get; set; }
    public string _Licence { get; set; }
    public bool _Superuser { get; set; }
    public bool _Activated { get; set; }
    public bool _Additional_user { get; set; }
    public int _Main_user { get; set; }
    public string _Statuss { get; set; }

    static SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);


    // Lietotāja rediģēšana: Atlasa visus datus par lietotāju no MyUsers.aspx EDIT un Default.aspx EDIT uz MyUser.aspx
    public static _User EditUser(int ind)
    {
        _User user = new _User();
        SqlCommand selCmd = new SqlCommand("SELECT * FROM users WHERE id = @id", conn);
        selCmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int)).Value = ind;
        conn.Open();
        SqlDataReader dr = selCmd.ExecuteReader();
        while (dr.Read())
        {
            user._Id = (int)dr[0];
            user._Name = dr[1].ToString();
            user._SecondName = dr[2].ToString();
            user._Login = dr[3].ToString();
            user._Password = dr[4].ToString();
            user._Email = dr[5].ToString();
            user._ID_Licence = (int)dr[6];
            user._Superuser = (bool)dr[7];
            user._Activated = (bool)dr[8];
            user._Additional_user = (bool)dr[9];
            user._Statuss = dr[10].ToString();
        }
        dr.Close();
        conn.Close();
        return user;
    }

    //Lietotāja atlasīšana Default.aspx priekš USER kolonnas
    public static _User SelectUser(string id)
    {
        _User user = new _User();
        SqlCommand selCmd = new SqlCommand("SELECT * FROM USERS WHERE id=@id", conn);
        selCmd.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = id;
        conn.Open();
        SqlDataReader dr = selCmd.ExecuteReader();
        while (dr.Read())
        {
            user._Id = (int)dr[0];
            user._Name = dr[1].ToString();
            user._SecondName = dr[2].ToString();
        }
        dr.Close();
        conn.Close();
        return user;
    }

    public static _User Fillme(SqlDataReader dr)
    {
        _User users = new _User();
        users._Id = (int)dr[0];
        users._Name = dr[1].ToString();
        users._SecondName = dr[2].ToString();
        users._Login = dr[3].ToString();
        users._Password = dr[4].ToString();
        users._Email = dr[5].ToString();
        users._Licence = _Licence_type.SelectLicType(dr[6].ToString())._licence_type;
        users._Superuser = (bool)dr[7];
        users._Activated = (bool)dr[8];
        users._Additional_user = (bool)dr[9];
        users._Statuss = dr[10].ToString();
        return users;
    }

    public static _User FillUsers(SqlDataReader drr)
    {
        _User users = new _User();

        users._Id = (int)drr[0];
        users._Name = drr[1].ToString();
        users._SecondName = drr[2].ToString();
        users._Login = drr[3].ToString();
        users._Password = drr[4].ToString();
        users._Email = drr[5].ToString();
        users._Licence = _Licence_type.SelectLicType(drr[6].ToString())._licence_type;
        users._Superuser = (bool)drr[7];
        users._Activated = (bool)drr[8];
        users._Additional_user = (bool)drr[9];
        users._Main_user = (int)drr[10];
        users._Statuss = drr[11].ToString();
        return users;
    }

    //Lietotāja datu atlasīšana kurš ir patreiz ielogojies MyUsers.aspx
    public static List<_User> SelME(string main_id)
    {
        List<_User> me = new List<_User>();
        SqlCommand selCmd = new SqlCommand("SELECT * FROM USERS WHERE id=@main_user", conn);
        selCmd.Parameters.Add(new SqlParameter("@main_user", SqlDbType.Int)).Value = main_id;
        conn.Close();
        conn.Open();
        SqlDataReader dr = selCmd.ExecuteReader();
        while (dr.Read())
        {
            me.Add(Fillme(dr));
        }
        dr.Close();
        conn.Close();
        return me;
    }

    //Lietotāja lietotāju atlasīšana kurš ir patreiz ielogojies Default.aspx un MyUsers.aspx
    public static List<_User> SelUser(string main_id)
    {
        List<_User> user_lis = new List<_User>();
        SqlCommand selCmd = new SqlCommand("SELECT * FROM USERS WHERE main_user=@main_user", conn);
        selCmd.Parameters.Add(new SqlParameter("@main_user", SqlDbType.Int)).Value = main_id;
        conn.Open();
        SqlDataReader drr = selCmd.ExecuteReader();
        while (drr.Read())
        {
            user_lis.Add(FillUsers(drr));
        }
        drr.Close();
        conn.Close();
        return user_lis;
    }
    
    public string MD5String(string input)
    {
        var md5 = new MD5CryptoServiceProvider();
        byte[] inputBytes = Encoding.ASCII.GetBytes(input);
        byte[] hashBytes = md5.ComputeHash(inputBytes);
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < hashBytes.Length; i++)
        {
            sb.Append(hashBytes[i].ToString("x2"));
        }
        return sb.ToString();
    }

    //Lietotāja ielogošanās Login.aspx
    public string Login(string login, string password)
    {
        string Id = null;
        SqlCommand selCmd = new SqlCommand("SELECT ID, password FROM users WHERE login = @login", conn);
        selCmd.Parameters.Add(new SqlParameter("@login", System.Data.SqlDbType.NVarChar)).Value = login;
        conn.Open();
        SqlDataReader dr = selCmd.ExecuteReader();
        if (dr.HasRows)
        {
            dr.Read();            
            if (MD5String(password) == dr.GetString(1))
            {
                Id = dr[0].ToString();
            }
        }
        dr.Close();
        conn.Close();
        return Id;
    }

    //Lietotāja reģistrēšana Registration.aspx
    public static _User Register(string name, string secondname, string login, string password, string emil, string idLicence)
    {
        _User user = new _User();
        SqlCommand addCmd = new SqlCommand("INSERT INTO USERS (NAME, SECONDNAME, LOGIN, PASSWORD, EMAIL, ID_LICENCE, ACTIVATED, ADDITIONAL_USER, SUPERUSER, STATUSS) " + "  VALUES (@NAME, @SECONDNAME, @LOGIN, @PASSWORD, @EMAIL, @ID_LICENCE, @ACTIVATED, @ADDITIONAL_USER, @SUPERUSER, @STATUSS);", conn);
        addCmd.Parameters.Add(new SqlParameter("@NAME", System.Data.SqlDbType.NVarChar)).Value = name;
        addCmd.Parameters.Add(new SqlParameter("@SECONDNAME", System.Data.SqlDbType.NVarChar)).Value = secondname;
        addCmd.Parameters.Add(new SqlParameter("@LOGIN", System.Data.SqlDbType.NVarChar)).Value = login;
        addCmd.Parameters.Add(new SqlParameter("@PASSWORD", System.Data.SqlDbType.NVarChar)).Value = password;
        addCmd.Parameters.Add(new SqlParameter("@EMAIL", System.Data.SqlDbType.NVarChar)).Value = emil;
        addCmd.Parameters.Add(new SqlParameter("@ID_LICENCE", System.Data.SqlDbType.Int)).Value = idLicence;
        addCmd.Parameters.Add(new SqlParameter("@ACTIVATED", System.Data.SqlDbType.NVarChar)).Value = true;
        addCmd.Parameters.Add(new SqlParameter("@ADDITIONAL_USER", System.Data.SqlDbType.NVarChar)).Value = true;
        addCmd.Parameters.Add(new SqlParameter("@SUPERUSER", System.Data.SqlDbType.NVarChar)).Value = true;
        addCmd.Parameters.Add(new SqlParameter("@STATUSS", System.Data.SqlDbType.NVarChar)).Value = "Active";
        conn.Open();
        addCmd.ExecuteNonQuery();
        conn.Close();
        return user;
    }

    //Lietotāja pievienošana AddUser.aspx
    public static _User Insert(string name, string secondname, string login, string password, string emil, string idLicence, string superuser, string activated, string additionalUser, string mainUser)
    {
        _User user = new _User();
        SqlCommand addCmd = new SqlCommand("INSERT INTO USERS (NAME, SECONDNAME, LOGIN, PASSWORD, EMAIL, ID_LICENCE, ACTIVATED, ADDITIONAL_USER, SUPERUSER, MAIN_USER, STATUSS) " + "  VALUES (@NAME, @SECONDNAME, @LOGIN, @PASSWORD, @EMAIL, @ID_LICENCE, @ACTIVATED, @ADDITIONAL_USER, @SUPERUSER, @MAIN_USER, @STATUSS);", conn);
        addCmd.Parameters.Add(new SqlParameter("@NAME", System.Data.SqlDbType.NVarChar)).Value = name;
        addCmd.Parameters.Add(new SqlParameter("@SECONDNAME", System.Data.SqlDbType.NVarChar)).Value = secondname;
        addCmd.Parameters.Add(new SqlParameter("@LOGIN", System.Data.SqlDbType.NVarChar)).Value = login;
        addCmd.Parameters.Add(new SqlParameter("@PASSWORD", System.Data.SqlDbType.NVarChar)).Value = password;
        addCmd.Parameters.Add(new SqlParameter("@EMAIL", System.Data.SqlDbType.NVarChar)).Value = emil;
        addCmd.Parameters.Add(new SqlParameter("@ID_LICENCE", System.Data.SqlDbType.Int)).Value = idLicence;
        addCmd.Parameters.Add(new SqlParameter("@ACTIVATED", System.Data.SqlDbType.NVarChar)).Value = activated;
        addCmd.Parameters.Add(new SqlParameter("@ADDITIONAL_USER", System.Data.SqlDbType.NVarChar)).Value = additionalUser;
        addCmd.Parameters.Add(new SqlParameter("@SUPERUSER", System.Data.SqlDbType.NVarChar)).Value = superuser;
        addCmd.Parameters.Add(new SqlParameter("@MAIN_USER", System.Data.SqlDbType.Int)).Value = mainUser;
        addCmd.Parameters.Add(new SqlParameter("@STATUSS", System.Data.SqlDbType.NVarChar)).Value = "Active";
        conn.Open();
        addCmd.ExecuteNonQuery();
        conn.Close();
        return user;
    }
    
    //Lietotāja rediģēšana MyUser.aspx
    public static _User Update(string id, string name, string secondname, string login, string password, string emil, string id_licence, string superuser, string activated, string additional_user, string main_user)
    {
        _User user = new _User();
        SqlCommand UpCmd = new SqlCommand("UPDATE USERS SET NAME=@NAME, SECONDNAME=@SECONDNAME, LOGIN=@LOGIN, PASSWORD=@PASSWORD, EMAIL=@EMAIL, ID_LICENCE=@ID_LICENCE, ACTIVATED=@ACTIVATED, SUPERUSER=@SUPERUSER, ADDITIONAL_USER=@ADDITIONAL_USER WHERE ID=@ID AND MAIN_USER=@MAIN_USER", conn);
        UpCmd.Parameters.Add(new SqlParameter("@ID", System.Data.SqlDbType.Int)).Value = id;
        UpCmd.Parameters.Add(new SqlParameter("@NAME", System.Data.SqlDbType.NVarChar)).Value = name;
        UpCmd.Parameters.Add(new SqlParameter("@SECONDNAME", System.Data.SqlDbType.NVarChar)).Value = secondname;
        UpCmd.Parameters.Add(new SqlParameter("@LOGIN", System.Data.SqlDbType.NVarChar)).Value = login;
        UpCmd.Parameters.Add(new SqlParameter("@PASSWORD", System.Data.SqlDbType.NVarChar)).Value = password;
        UpCmd.Parameters.Add(new SqlParameter("@EMAIL", System.Data.SqlDbType.NVarChar)).Value = emil;
        UpCmd.Parameters.Add(new SqlParameter("@ID_LICENCE", System.Data.SqlDbType.Int)).Value = id_licence;
        UpCmd.Parameters.Add(new SqlParameter("@ACTIVATED", System.Data.SqlDbType.NVarChar)).Value = activated;
        UpCmd.Parameters.Add(new SqlParameter("@ADDITIONAL_USER", System.Data.SqlDbType.NVarChar)).Value = additional_user;
        UpCmd.Parameters.Add(new SqlParameter("@SUPERUSER", System.Data.SqlDbType.NVarChar)).Value = superuser;
        UpCmd.Parameters.Add(new SqlParameter("@MAIN_USER", System.Data.SqlDbType.Int)).Value = main_user;
        conn.Open();
        UpCmd.ExecuteNonQuery();
        conn.Close();
        return user;
    }

    //Lietotāja rediģēšana MyUser.aspx
    public static _User UpdateME(string id, string name, string secondname, string login, string password, string emil, string id_licence, string superuser, string activated, string additional_user)
    {
        _User user = new _User();
        SqlCommand UpCmd = new SqlCommand("UPDATE USERS SET NAME=@NAME, SECONDNAME=@SECONDNAME, LOGIN=@LOGIN, PASSWORD=@PASSWORD, EMAIL=@EMAIL, ID_LICENCE=@ID_LICENCE, ACTIVATED=@ACTIVATED, SUPERUSER=@SUPERUSER, ADDITIONAL_USER=@ADDITIONAL_USER WHERE ID=@ID ", conn);
        UpCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = id;
        UpCmd.Parameters.Add(new SqlParameter("@NAME", SqlDbType.NVarChar)).Value = name;
        UpCmd.Parameters.Add(new SqlParameter("@SECONDNAME", SqlDbType.NVarChar)).Value = secondname;
        UpCmd.Parameters.Add(new SqlParameter("@LOGIN", SqlDbType.NVarChar)).Value = login;
        UpCmd.Parameters.Add(new SqlParameter("@PASSWORD", SqlDbType.NVarChar)).Value = password;
        UpCmd.Parameters.Add(new SqlParameter("@EMAIL", SqlDbType.NVarChar)).Value = emil;
        UpCmd.Parameters.Add(new SqlParameter("@ID_LICENCE", SqlDbType.Int)).Value = id_licence;
        UpCmd.Parameters.Add(new SqlParameter("@ACTIVATED", SqlDbType.NVarChar)).Value = activated;
        UpCmd.Parameters.Add(new SqlParameter("@ADDITIONAL_USER", SqlDbType.NVarChar)).Value = additional_user;
        UpCmd.Parameters.Add(new SqlParameter("@SUPERUSER", SqlDbType.NVarChar)).Value = superuser;
        conn.Open();
        UpCmd.ExecuteNonQuery();
        conn.Close();
        return user;
    }

    //Lietotāja dzēšana no MyUsers.aspx
    public static _User DeleteUser(int id)
    {
        _User user = new _User();
        List<int> user_users = new List<int>();
        SqlCommand SelCmd = new SqlCommand("SELECT id FROM USERS WHERE main_user = @main_user", conn);
        SelCmd.Parameters.Add(new SqlParameter("@main_user", SqlDbType.Int)).Value = id;
        conn.Open();
        SqlDataReader dr = SelCmd.ExecuteReader();
        while (dr.Read())
        {
            user_users.Add(Convert.ToInt32(dr["id"].ToString()));
        }
        dr.Close();
        conn.Close();

        foreach (int userIP in user_users)
        {
            SqlCommand UpCmd = new SqlCommand("UPDATE USERS SET MAIN_USER=NULL WHERE ID=@ID", conn);
            UpCmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = userIP;
            try
            {
                conn.Open();
                UpCmd.ExecuteNonQuery();
                conn.Close();
            }
            catch { System.Web.HttpContext.Current.Response.Write("<SCRIPT LANGUAGE='JavaScript'>alert('Error in reading data!')</SCRIPT>"); }
        }

        SqlCommand UpCmd1 = new SqlCommand("UPDATE USERS SET STATUSS=@STATUSS WHERE ID=@ID ", conn);
        UpCmd1.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = id;
        UpCmd1.Parameters.Add(new SqlParameter("@STATUSS", SqlDbType.NVarChar)).Value = "Deleted";
        conn.Open();
        UpCmd1.ExecuteNonQuery();
        conn.Close();
        return user;
    }
}